import csv
import sqlite3
import argparse
import os
from typing import List, Any, Optional, Callable, Tuple

def infer_type(value: str) -> type:
    """推断字符串值的 Python 类型"""
    if not value.strip():
        return str
    try:
        int(value)
        return int
    except ValueError:
        pass
    try:
        float(value)
        return float
    except ValueError:
        pass
    return str

def map_pytype_to_sqlite(pytype: type) -> str:
    if pytype is int:
        return "INTEGER"
    elif pytype is float:
        return "REAL"
    else:
        return "TEXT"

def analyze_column_types(rows: List[List[str]], col_index: int, sample_size: int = 1000) -> str:
    samples = [row[col_index] for row in rows[:sample_size] if col_index < len(row)]
    if not any(s.strip() for s in samples):
        return "TEXT"
    
    has_str = False
    has_float = False
    for val in samples:
        t = infer_type(val)
        if t is str:
            has_str = True
            break
        elif t is float:
            has_float = True
    
    if has_str:
        return "TEXT"
    elif has_float:
        return "REAL"
    else:
        return "INTEGER"

def create_table_from_csv(
    csv_file: str,
    db_file: str,
    table_name: str = "data",
    csv_reader_post_handler: Optional[Callable[[List[str], List[List[str]]], Tuple[List[str], List[List[str]]]]] = None
):
    """
    将 CSV 转换为 SQLite 表，支持自定义后处理
    
    :param csv_file: 输入 CSV 文件路径
    :param db_file: 输出 SQLite 数据库路径
    :param table_name: 表名
    :param csv_reader_post_handler: 可选回调函数，
        签名: (headers: List[str], rows: List[List[str]]) -> (new_headers, new_rows)
        用于在读取 CSV 后、写入 DB 前修改数据
    """
    # 1. 读取原始 CSV
    with open(csv_file, 'r', encoding='utf-8') as f:
        reader = csv.reader(f)
        headers = next(reader)
        rows = list(reader)

    if not rows:
        raise ValueError("CSV 文件无数据行")

    # 2. 如果提供了后处理器，调用它
    if csv_reader_post_handler is not None:
        print("🔧 应用自定义 csv_reader_post_handler...")
        headers, rows = csv_reader_post_handler(headers, rows)
        if not isinstance(headers, list) or not all(isinstance(h, str) for h in headers):
            raise TypeError("Handler 返回的 headers 必须是 List[str]")
        if not isinstance(rows, list) or not all(isinstance(r, list) for r in rows):
            raise TypeError("Handler 返回的 rows 必须是 List[List[str]]")
        if rows and len(rows[0]) != len(headers):
            raise ValueError("Handler 返回的 rows 列数必须与 headers 一致")

    # 3. 推断列类型
    column_types = []
    for i, col_name in enumerate(headers):
        sql_type = analyze_column_types(rows, i)
        column_types.append(sql_type)
        print(f"列 '{col_name}' → {sql_type}")

    # 4. 写入 SQLite
    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()

    columns_def = ", ".join([f'"{col}" {typ}' for col, typ in zip(headers, column_types)])
    create_sql = f'CREATE TABLE IF NOT EXISTS "{table_name}" ({columns_def});'
    cursor.execute(create_sql)

    placeholders = ", ".join(["?" for _ in headers])
    insert_sql = f'INSERT INTO "{table_name}" VALUES ({placeholders})'
    cursor.executemany(insert_sql, rows)

    conn.commit()
    conn.close()

    print(f"\n✅ 成功将 '{csv_file}' 转换为 SQLite 表 '{table_name}'（共 {len(rows)} 行）")
    print(f"   数据库文件: {db_file}")

# ========================
# 示例：命令行使用 + 自定义 handler
# ========================

def example_handler(headers: List[str], rows: List[List[str]]) -> Tuple[List[str], List[List[str]]]:
    """
    示例后处理器：
    - 将 '姓名' 列重命名为 'name'
    - 删除 '备注' 列（如果存在）
    - 将 '年龄' 列的空值替换为 '0'
    """
    import copy
    new_headers = copy.deepcopy(headers)
    new_rows = copy.deepcopy(rows)

    # 重命名
    if '姓名' in new_headers:
        idx = new_headers.index('姓名')
        new_headers[idx] = 'name'

    # 删除列
    if '备注' in new_headers:
        idx = new_headers.index('备注')
        new_headers.pop(idx)
        new_rows = [row[:idx] + row[idx+1:] for row in new_rows]

    # 清洗年龄列
    if '年龄' in new_headers:
        age_idx = new_headers.index('年龄')
        for row in new_rows:
            if age_idx < len(row) and not row[age_idx].strip():
                row[age_idx] = '0'

    return new_headers, new_rows